Data Adapters

Data Adapters specify the kind of data used within a dashboard. Once the Data Adapter is configured and pointing to the appropriate data, attach it to a Dashboard component in order to display it on a dashboard.

General Properties

Name

The name of the Data Adapter.

Description

A quick description of the Data Adapter.

Maintenance Unit

The Maintenance Unit to which the Data Adapter belongs.

There are three command types:

Cube View

Choose a Cube View as the source of a Data Adapter.  Additional options can be selected here to include supplemental information for the resulting tables.  However, adding on to what is defaulted may have a slight impact on performance:

Cube View

This command type allows for a pre-configured Cube View to be the Data Source for a dashboard.  Click and begin typing the name of the Cube View in the blank field.  As the first few letters are typed, the names are filtered making it easier to find and select the one desired.  If the name is unknown, expand a Cube View Group and scroll through the list to select the correct one.  Once the Cube View is selected, click CTRL and Double Click.  This will enter the correct name into the appropriate field. 

Data Table Per Cube View Row

At the creation of the Data Adapter, the default is set to True.  When set to True, a Data Table is created for each row in the dashboard.  This allows for conditional formatting per Cube View row.  Set this to False in order to merge the rows into one table.  This will omit any undefined Cube View row. 

Include Title

At the creation of the Data Adapter, the default is set to False.  When set to True, the title will be displayed from the Report section of the Cube View as the title for the dashboard.  Settings are True or False.  

Include Header Left Label 1-4

At the creation of the Data Adapter, the default is set to False.  When set to True, the left header labels will be displayed from the report section of the Cube View for the dashboard. 

Include Header Center Label 1-4

At the creation of the Data Adapter, the default is set to False.  When set to True, the center header labels will be displayed from the report section of the Cube View for the dashboard. 

Include Header Right Label 1-4

At creation of the Data Adapter, the default is set to False.  When set to True, the right header labels will be displayed from the report section of the Cube View for the dashboard. 

Include… POV

If set to True, the POV information for the Cube, Entity and all other Dimensions are included. Use these if the report or dashboard needs this information. 

Include Member Details

If set to True, additional Member property details are included in the results. 

Include Row Navigation Link

If set to True, this data Adapter will include a row navigation link from a Cube View.

Include HasData Status

Includes additional true/false data on whether the row of results contains data for filtering purposes.  Settings are True or False.

Include … View Member Text

This determines whether different Data Attachment text is going to be part of the results. This is necessary for showing text in a Data Explorer object or for using a Waterfall Chart and wanting to optionally show comments. Settings are True or False.

Results Table Name

This specifies the name of the resulting table generated when the Data Adapter is run, otherwise it will default with a name of Table. 

Cube View MD

Choose a Cube View MD (Multi-Dimensional) as the source of a Data Adapter.  This Command Type will return the selected Cube View as a Multi-Dimensional Fact Table versus the reporting table that is returned by the Cube View Command Type. The results of the Cube View MD are Dimensions (Entity, Consolidation, Scenario, time, View, Account, Flow, Origin, IC, UD1-UD8) as columns. This simplifies the report building process in the BI Designer, Pivot Grid, and dashboard development. There are additional Loop Parameter options that can be selected here to include incremental information from the modified Cube View definition in the resulting tables. Adding on to what is defaulted, however, may have a slight impact on performance.           

Cube View MD Data Adapter example   

           

Results Table from the Cube View MD Data Adapter example above.

Cube View

This command type allows for a pre-configured Cube View to be the Data Source for a dashboard. Click and begin typing the name of the Cube View in the blank field. As the first few letters are typed, the names are filtered making it easier to find and select the one desired. If the name is unknown, expand a Cube View Group and scroll through the list to select the correct one. Once the Cube View is selected, click CTRL and Double Click. This will enter the correct name into the appropriate field. 

Results Table Name

This specifies the name of the resulting table generated when the Data Adapter is run, otherwise it will default with a name of Table. It is optional but highly recommended that a name is defined here to distinguish between different Data Adapters if there are many assigned when using the BI Designer.

For example; tbl_OperatingExpenses can be used to identify the Results Table Name and the Name of the Data Adapter (OperatingExpenses_CVMD in this example) associated with this table.

Add Start End Calendar Time

When set to True, the Data Table incorporates the Start and End Date used in the POV / Time Profile for the Cube View and creates two additional columns in a Date/Time Field Type Format; StartDate, EndDate for each row in the dashboard. This allows for the ability to utilize the Date Grouping functions in the BI Designer. Set this to False to not add or display these Date Time fields. 

Entity

At the creation of the Data Adapter, the default is set to Name And Description. This will display both the Name and Description of the Entity. When set to Name, the Entity Name will be displayed in the results table from the Cube View. When set to Description, the Entity Description will be displayed in the results table from the Cube View.

Consolidation

At the creation of the Data Adapter, the default is set to Name And Description. This will display both the Name and Description of the Consolidation. When set to Name, the Consolidation Name will be displayed in the results table from the Cube View.  When set to Description, the Consolidation Description will be displayed in the results table from the Cube View.

Scenario

At the creation of the Data Adapter, the default is set to Name And Description. This will display both the Name and Description of the Scenario. When set to Name, the Scenario Name will be displayed in the results table from the Cube View.  When set to Description, the Scenario Description will be displayed in the results table from the Cube View.

Time

At the creation of the Data Adapter, the default is set to Name And Description. This will display both the Name and Description of the Time. When set to Name, the Time Name will be displayed in the results table from the Cube View.  When set to Description, the Time Description will be displayed in the results table from the Cube View.

View

At the creation of the Data Adapter, the default is set to Name And Description. This will display both the Name and Description of the View. When set to Name, the View Name will be displayed in the results table from the Cube View.  When set to Description, the View Description will be displayed in the results table from the Cube View.

Account

At the creation of the Data Adapter, the default is set to Name And Description. This will display both the Name and Description of the Account. When set to Name, the Account Name will be displayed in the results table from the Cube View.  When set to Description, the Account Description will be displayed in the results table from the Cube View.

Flow

At the creation of the Data Adapter, the default is set to Name And Description. This will display both the Name and Description of the Flow. When set to Name, the Flow Name will be displayed in the results table from the Cube View.  When set to Description, the Flow Description will be displayed in the results table from the Cube View.

Origin

At the creation of the Data Adapter, the default is set to Name And Description. This will display both the Name and Description of the Origin. When set to Name, the Origin Name will be displayed in the results table from the Cube View.  When set to Description, the Origin Description will be displayed in the results table from the Cube View.

IC

At the creation of the Data Adapter, the default is set to Name And Description. This will display both the Name and Description of the IC. When set to Name, the IC Name will be displayed in the results table from the Cube View. When set to Description, the IC Description will be displayed in the results table from the Cube View.

UD1-UD8

At the creation of the Data Adapter, the default is set to Name And Description. This will display both the Name and Description of the UD1-UD8. When set to Name, the UD1-UD8 Name will be displayed in the results table from the Cube View.  When set to Description, the UD1-UD8 Description will be displayed in the results table from the Cube View.

Loop Parameters

This section allows changes to be made to the output of a Cube View definition being used in a table for reporting. The Loop Parameter filters the results and considers the additional parameters to be passed to the Cube View definition and add those results to the table accordingly.

The parameter(s) overrides the POV.  So, if the Entity POV is set to CT, and the loop filter parameters are set to NY, MA, and NJ, then data for those will be returned and NOT CT. A Loop must be used in order to change parameters.                           

For example, a Loop Parameter may be used to loop through a list of Entities in the Cube View definition and return multiple Entities for that specific Cube View. The Dimension Type and Member Filters should be added here to pass along the appropriate Loop (e.g. Dimension Type=Entity, Member Filter= E#US.Base) which applies to each Entity included in the Loop.

NOTE: It is recommended to not loop on any Dimensions that already exist in the Cube View’s rows or columns.

At the creation of the Data Adapter, the default for each Dimension Type (1 & 2) are set to (Not Used) and Member Filter (1&2) are greyed out. This will display the results without any consideration of additional parameters to pass to the query.  When the Dimension Types are set along with the Member Filters, the results will consider the additional parameters to be passed to the Cube View definition and add those results to the table accordingly.

Dimension Type 1

The Dimension Type containing the list of Members.  e.g., Entity or Account

At the creation of the Data Adapter, the default for each Dimension Type (1 & 2) are set to (Not Used) and Member Filter (1&2) are greyed out. This will display the results without any consideration of additional parameters to pass to the query. When the Dimension Types are set along with the Member Filters, the results will consider the additional parameters to be passed to the Cube View definition and add those results to the table accordingly.

Member Filter 1

Enter a Member Filter here to determine what is seen in the Parameter.

At the creation of the Data Adapter, the default for each Dimension Type 1 is set to (Not Used) and Member Filter 1 is greyed out. This will display the results without any consideration of additional parameters to pass to the query. When the Dimension Types are set along with the Member Filters, the results will consider the additional parameters to be passed to the Cube View definition and add those results to the table accordingly.

The name of the Dimension containing the list of Members. Start typing in the blank field OR click the ellipsis button in order to launch the Member Script Builder and enter a Member Script to change the Cube View definition. The example below is changing the POV for the Products.
Example: UD2; U2#Top.Base

Dimension Type 2

At the creation of the Data Adapter, the default for Dimension Type 2 is set to (Not Used) and Member Filter 2 is greyed out. This will display the results without any consideration of additional parameters to pass to the query. When the Dimension Types are set along with the Member Filters, the results will consider the additional parameters to be passed to the Cube View definition and add those results to the table accordingly.

Member Filter 2

Enter a Member Filter here to determine what is seen in the Parameter.

At the creation of the Data Adapter, the default for Dimension Type 2 is set to (Not Used) and Member Filter 2 is greyed out. This will display the results without any consideration of additional parameters to pass to the query. When the Dimension Types are set along with the Member Filters, the results will consider the additional parameters to be passed to the Cube View definition and add those results to the table accordingly.

The name of the Dimension containing the list of Members. Start typing in the blank field OR click the ellipsis button in order to launch the Member Script Builder and enter a Member Script to change the Cube View definition. The example below is changing the POV for the Products.
Example: UD2; U2#Top.Base

Dimension Leveling

The Dimension Level property setting in the Cube View MD Data Adapter displays dimensional data as a hierarchical tree in the BI Viewer. Dimension leveling allows you to display data in a hierarchical structure into which you can drill down to view child data. After the data is leveled, you can use the BI Viewer to view data as a tree, a pivot table, a chart, or a grid.

Prerequisites

To use dimension leveling to display hierarchical data, you must first create a cube view MD data adapter with data.

Create the Data Adapter

Cube View MD Data adapters allow you to pull data from a Cube View. To create a data adapter:

  1. Inside the OneStream application, click the Application Dashboards tab at the bottom of the screen.
  2. Expand the appropriate Dashboard Maintenance Unit.
  3. Click the Data Adapter label.
  4. Click the Create Data Adapter button on the toolbar.
  5. In the Name field, type the name of the data adapter.
  6. In the Command Type field, click the drop-down arrow and select Cube View MD.
  7. Click the Edit button ()at the far right of the Cube View field.
  8. In the Object Lookup dialog box, select the appropriate Cube View and then click OK.
  9. In the Dimension to Level field, click the drop-down arrow and select the appropriate leveling option. With this step, you are leveling on this dimension, which in this case is Entity.
  10. Select one of the following:
    • Outermost Row - hierarchy uses the first row and first level (of row) definition.
    • Outermost Column - hierarchy uses the first column and first level (of column) definition.
    • Both - hierarchy uses the first row and first level definition and uses the first column and first level (of column) definition.

      NOTE: When Data Adapter is run, the Data Table will generate the additional columns for the levels including a column(s) to determine the status of the level:

    • RowMemberIsBase = 1: Row Member contains the Base level of data  RowMemberIsBase = 0: Row Member is not the Base level of data  ColumnMemberIsBase= 1: Column Member contains the Base level of data  ColumnMemberIsBase= 0: Column Member is not the Base level of data

  11. Click Save.
  12. Test the data adapter by clicking Test Data Adapter. The Data Preview dialog box displays the table data.
  13. NOTE: When Data Adapter runs, the Data Table generates the additional columns for the levels including columns to determine the status of the level.

    Each dimension leveled column is prefixed with an E to indicate it is an Entity, followed by a level number. A RowMemberIsBase value of 1 indicates that there is no remaining child data.

  14. Click Close.

View Results in the BI Viewer

You can view data in various ways using the BI Viewer. For example, you can configure the BI Viewer to display data in pivot tables and charts at the same time. To configure the BI Viewer:

  1. Click the Application Dashboard tab at the bottom of the window.
  2. Create a BI Viewer component:
    1. Click the Components label in the tree.
    2. Click the Create Dashboard Component button on the toolbar.
    3. In the Create Dashboard Component dialog box, click BI Viewer and then click OK
    4. On the Component Properties tab, in the Name field, type the name of the new component.
    5. Click the Data Adapters tab at the top of the window.
    6. Click the Add Dashboard Component button on the toolbar.
    7. In the Add Data Adapter dialog box, select the appropriate data adapter and then click OK.
    8. Click Save.
  3. Design the BI Viewer dashboard:
    1. Click the BI Designer tab at the top of the window.
    2. In the Data Source field, click the drop-down arrow and select the data source from which to pull data. This is usually a table.
    3. Drag each dimension level that you want to view from the Table view into the Data Items column under Dimensions.

    4. If you want to filter items based on whether they are base items, drag the RowNumberIsBase item into the Data Items column but under Hidden Data Items. This item will not display in the resulting dashboard but will be available for you to filter on if necessary.

    5. From the BI Designer ribbon, select the type of dashboard item you want to view in the dashboard. For example, select Filter Element > Tree View. In the resulting tree view in the dashboard notice that you can expand parent members down to their base child members.

      If you add a Grid, all levels of the parent entity display in their own columns. If you want to see a pivot table, select Pivot from the ribbon and notice that each level is expandable similar to tree view.

Conclusion

With Dimension leveling, you can view data in an easy-to-understand, hierarchical format. With the BI Viewer, you can design various dashboard items in which to view the dimension leveled data.

Method Query

TIP: To view an example Method Query, leave the Method Query field blank, click Save, and then click Test Data Adapter

Method Type

BusinessRule

Use the Business Rule option when creating a custom rule to incorporate within a Method Query.  The Business Rule is used as the first set of {} within the Method Query.  


Example Method Query: {XFR_DataUnitCompare}{DataUnitComparisonDataSet}
{Cube1=|!Members_Cubes!|, Entity1=|!Members_Entities_AllDims_Base!|, Parent1=[], Cons1=|!Members_Cons_Statutory!|,
Scenario1=|!Members_Scenarios_AllDims_Base!|, Time1=|!Members_Time_WFYear_ Base!|, Cube2=|!Members_Cubes!|, Entity2=|!Members_Entities_AllDims_Base!|, Parent2=[], Cons2=|!Members_Cons_Statutory!|, Scenario2=|!Members_Scenarios_ AllDims_Base_Var!|, Time2=|!Members_Time_WFYear_Base!|, View= |!Members_View_Numeric!|, SuppressMatches=|!DataUnit_SuppressMatches!|}

CertificationforWorkflowUnit

This lists all Certification Questions for the particular Workflow Unit.
Example Method Query: 

{Workflow Profile Name}{Scenario Name}{Time Name}{Include Descendants}{} or
 {Dallas}{Actual}{2011M2}{true}{}.

ConfirmationforWorkflowUnit

This lists the Confirmation Rules results for a particular Workflow Unit.
Example Method Query: 

{Workflow Profile Name}{Scenario Name}{Time Name}{Include Descendants}{Filter}
or {Montreal}{Actual}{2011M6}{true}{} Name}{Include Descendants}{Filter} or 
{Montreal}{Actual}{2011M6}{true}{}

DataUnit

This returns all rows of data related to the specified Data Unit (i.e. Cube, Entity, Parent, Consolidation Member, Scenario, Time and View).
Example Method Query:

{Cube}{Entity}{Parent}{Cons}{Scenario}{Time}{View} {True}
{Empty String or Filter Expression}

DataUnitComparison

This returns all rows from two different Data Units specified for comparison purposes.
Example Method Query:

{Cube1}{Entity1}{Parent1}{Cons1}{Scenario1}{Time1}
{Cube2}{Entity2}{Parent2}{Cons2}{Scenario2}{Time2}{View}{True}{True}{ Empty String or Filter Expression}

Excel

This returns data sourced from an Excel file. Parameters for method type 'ExcelFile' should include the following, each enclosed in curly braces:

{FileSourceType}{UrlOrFullFileName}{Sheet}{NamedRange}{UseFirstRowForColumnNames}{IncludeNumericColumns}{Filter},

Example:

{Application}{Documents/Public/MyExcelfile.xfDoc.xlsx}{Sheet1 or Empty}
{MyDataRange or Empty}{False}{False}{}.

FormsStatusForWorkflowUnit

This lists detailed information about the Forms for a particular Workflow Unit. Example Method Query: 

{Workflow Profile Name}{Scenario Name}{Time Name}{Form Status}{Filter} or 
{Houston}{Actual}{2011M1}{All}{}

Groups

This returns the Group ID, Name, Description and whether or not this is an Exclusion Group. For example:

{GroupName = 'FinanceGroup'}

GroupsforUsers

Select User properties and all of the Groups to which the user. This returns the same group properties as the Group Method Query.
For example:

{UserName = 'Administrator'}{}

ICMatchingforWorkflowUnit

This returns a detailed Intercompany Matching Report table for the given Workflow Unit and several other Parameters. The Parameters override what is set up in the Workflow Profile. For example:

{Workflow Profile Name}{Scenario Name}{Time Name}{Plug Account Override}{
Suppress Matches Override}{Tolerance Override}{Filter} or 
{Flint}{Actual}{2011M1}{Empty String or A#MyPlugAccount}{Empty String or
true/false}{Empty String or 0.0}{Empty String or Filter Expression}.

ICMatchingPlugAccountsforWorkflowUnit

This returns the list of Intercompany Plug Accounts for a given Workflow Profile and Scenario Type configured for the Workflow Profile.

Method Query Example

{MyWorkflowProfileName}{Actual}{2011M1}
{PlugAccount for Workflow Parameter Set (Exclude A#)}{Empty String
or C#MyCurrencyOverride}{Empty String or V#MyViewOverride}{Empty String or A#MyPlugAccountOverride}{Empty String or true/false}{Empty String or 0.0} {Empty String or E#MyEntityOverride}{Empty String or E#MyPartnerOverride} {Empty String or MyDetailDimsOverride (F#All:O#Top:U1#All:U2#All:U3#All: U4#All:U5#All:U6#All:U7#All:U8#All)}{Empty String or Filter Expression}

 

JournalforWorkflowUnit

This lists the Journals entered for a given Workflow Unit

{Workflow Profile Name}{Scenario Name}{Time Name}{Journal Status}{Filter} or
{Frankfurt}{Actual}{2011M3}{All}{}

Members

This returns Dimension ID, Member information such as ID, Name and Description, and a few other properties for the chosen Dimension and Member Filter.

{Account}{MyAccountDim}{A#Root.TreeDescendants}
{Empty String or Filter Expression}

UserCubeSliceRights

This lists each user’s Data Access settings on a given Cube.
For example:

{UserName}{CubeName}{Filter} or {AllUsers}{AllCubes}{}.

UserEntityRights

This returns the Cubes and Entities the user has access to according to the security settings under Entities. For example:

{UserName}{CubeName}{Filter} or {US Clubs Controller}{AllCubes}{}.

Users

This returns all properties associated for the chosen User Name.
For example:

{UserName = 'Administrator'}

UserScenarioRights

This returns all accessible Scenarios and many related Scenario properties for the chosen User Name filter and Cube.
Method query example: {AllUsers}{AllCubes}{Empty String or Filter Expression}

UserinGroups

This returns a list of Users and selected User properties for the chosen User Group. For example:

 {GroupName = 'FinanceGroup'}{}

UserWorkflowProfileRights

This lists the rights assigned to users for Workflow Profiles.
Method query example:

{User Name}{Workflow Cube Name}{Workflow Profile Type}{Filter} or
{Administrator}{GolfStream}{AllProfiles}{}.

WorkflowandEntityStatus

This returns properties for Workflow status, status code/description, last executed step, date/time information, completed steps, and data status for the chosen Workflow Unit including both the Workflow Profile level and individual Entity level.Method query example:

{MyWorkflowProfileName}{Actual}{2011M1}
{AllProfiles}{Descendants}{Empty String or Filter Expression}

WorkflowCalculationEntities

This lists the Entities that appear under Calculation Definitions for this Workflow Profile.

WorkflowConfirmationEntites

This lists the Entities that appear under Calculation Definitions with a Confirmed check box for this Workflow Profile.

WorkflowProfileandDescendantEntities

This creates a list of Entities and all descendants located under Entity Assignment for this Workflow Profile.

WorkflowProfileEntities

This creates a list of Entities located under Entity Assignment for this Workflow Profile.

WorkflowProfileRelatives

This lists related Workflow Profiles based on certain criteria.
For example: 

{Workflow Profile Name}{Scenario Name}{Time Name}{Workflow Profile Type}
{Relative Type}{Include Requesting Profile}{Filter} or {GolfStream}{Actual}
{2011M1}{AllProfiles}{Descendants}{true}{}.

WorkflowProfiles

This lists the Workflow Profiles. Example:
{WorkflowProfileType}{Filter} or {AllProfiles}{Type = 'InputAdjChild'}.

WorkflowStatus

This lists the status, lock status and last step completed of a given Workflow Unit. For example:


{Workflow Profile Name}{Scenario Name}{Time Name}{Workflow Profile Type} {Relative Type}{Filter} or {Houston}{Actual}{2011M1}{AllProfiles} {Descendants}{}

WorkflowStatusTwelvePeriod

This returns status value and text summary for 12 months for a given Workflow Profile, Scenario and Year. For Workflow Profile Type, options are AllProfiles, CubeRoot, Default, Review, BaseInput, InputImportChild, InputFormsChild, InputAdjChild and ParentInput. Example:

 {MyWorkflowProfileName}{Actual}{2011}{AllProfiles}
{Descendants}{Empty String or Filter Expression}

Method Query

The query ran for this Data Adapter.

Results Table Name

This specifies the name of the resulting table generated when the Data Adapter is run, otherwise it will default with a name of Table.

WorkflowLockHistory

Displays all Lock history details in a report for a given Workflow Profile with the ability to filter by Scenario, Time, Workflow Profile, Origin, Channel, Time, User and Lock Status. For example:

{Workflow Profile Name}{Scenario Name}{Time Name}{Workflow Profile Type}
{Relative Type}{Filter}

Method Query Parameter Options

Workflow Profile Type

AllProfiles, AllProfilesExceptInputChild, AllProfilesWithTemplates, AllTemplates, BaseAndParentInputProfile, BaseInput, ClassStandardProfiles, ClassTemplateProfiles, CubeRoot, Default, InputAdjChild, InputChildren, InputFormsChild, InputImportChild, NonInputParentProfiles, ParentInput and Review.

Workflow Profile Relative Type

Ancestors, Descendants, FirstLevelChildren, Siblings and Unrelated.

Workflow Status or Forms Status

Completed, HasError, InProcess, NoStatus, NotExecuted and Unknown.

Journal Status

Approved, Posted, Rejected, Submitted

SQL

A SQL query against either the Application or Framework database can be written as a Data Source.  Reference substitution variables such as |WFProfile| from within the SQL statement.

Database Location

Application

The current OneStream Application database where Stage and financial Cube data resides.

Framework

The connected OneStream Framework database where security and log data resides.

External

Any other database outside of OneStream.

External Database Connection

If External is the chosen Database Location, select the External Database Connection name here. This list is defined in the OneStream Server Configuration Utility.

SQL Query

The SQL statement ran for this Data Adapter.

Results Table List

This specifies the name of the resulting table generated when the Data Adapter is run, otherwise it will default with a name of Table.

BI Blend Adapter

The purpose of the BI Blend Adapter is to provide dashboard designers with a pre-defined interface to querying BI Blend tables.  The data sourced by the BI Blend Adapter can be used to design standard OneStream dashboard.

The BI Blend Workflow process is designed to generate large external database tables formatted in a column store index optimized for analytic reporting.  The overall number of records that may be generated by the BI Blend process may be too large for the Dashboard components to process.  Therefore, the designer should manage the returned dataset by defining an appropriate Where Clause to retrieve a “slice” of the BI Blend table which is suitable for the BI Blend Adapter.

Results Table Name

Define a table name to store the content of the BI Blend adapter query.

Table Info

This defines the name of the BI Blend table name to be queried.  This label supports the use of Parameters and Substitution Variables. 

Group By

This defines the source database columns, and their order, which are to be returned to the adapter.  These labels must match the names specified on the database.  Any field to be pivoted must be in the Group by list.

Data Field Aggregation Types

The Data Field Aggregation Type is used to identify which database column should be used as the Measure field.  The adapter can support multiple database columns as Measures.  Each defined measure can be defined to derive the results as a different Aggregation Type.  Parameters and/or Substitution Variables are supported.

  • Sum

  • Min

  • Max

  • Avg

  • Count

The syntax required is to define each Aggregation Type result as a unique key.  AggregationType = [databaseColumnName, Type]

  • AggType1 = [2018M1, Count], AggType2 = [2018M1, Sum]

  • AggType1 = [|WFYear|M1, Count]

Where Clause

The is used to “pre-filter” the table.  The BI Blend Adapter will query the source table and return all the results to the client for processing, such as for use in a Pivot Grid.  The size of the query must be managed to ensure the overall performance of the Dashboard report is optimized.   The Where Clause uses standard SQL syntax to define the filters which are applied to the query.

  • DatabaseColumn = ‘Text’

  • DatabaseColumn Like ‘2019%’  - As all columns beginning with 2019

  • DatabaseColumn1 = ‘CostCenter’ And DatabaseColumn2 = ‘Midwest’

Direct Load Reporting Data Adapter Method Types

Dashboard reporting against Direct Load Workflow activity is simplified with specialty Data Adapter Methods.

  • Direct Load Info: Retrieves results from the StageDirectLoadInformation table. Method query example:

     {Workflow Profile Name}{Scenario Name}{Time Name}{Filter}
  • Stage Summary Target Data: Query designed to automatically determine the method used to manage Summary Target Data, as Row or Blob, and appropriately return the results. Method query example:

    {Workflow Profile Name}{Scenario Name}{Time Name}{Filter}